package sg.edu.nus.iss.phoenix.presenterproducer.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import sg.edu.nus.iss.phoenix.dao.DBConstants;
import sg.edu.nus.iss.phoenix.presenterproducer.dao.ProducerDAO;
import sg.edu.nus.iss.phoenix.authenticate.entity.Role;
import sg.edu.nus.iss.phoenix.authenticate.entity.User;

public class ProducerDAOImpl implements ProducerDAO {
	
	Connection connection;
	
	public User createValueObject() {
		return new User();
	}
	
	
	public List<User> searchProducer(User valueObject,Role valueObject2) throws SQLException {
		List<User> searchResults = new ArrayList<User>();
		openConnection();
		boolean first = true;
		
		StringBuffer sql = new StringBuffer("SELECT a.user_name,a.country FROM user_mst AS a INNER JOIN user_role_mst AS b ON a.user_id = b.user_id WHERE 1=1 ");
		
		if (valueObject.getName() != null) {
			if (first){
				first = false;
			}
			sql.append(" AND a.user_name LIKE '").append(valueObject.getName()).append("%' ");
		}
		
		sql.append(" AND b.role_id = ").append(valueObject2.getRoleId());
		sql.append(" ORDER BY a.user_name ASC ");
		
		if (first)
			searchResults = new ArrayList<User>();
		else
			searchResults = listQuery(connection.prepareStatement(sql.toString()));
		closeConnection();
		return searchResults;
	}
	
	public List<User> searchAllProducer(Role valueObject) throws SQLException {
		
		List<User> searchResults = new ArrayList<User>();
		openConnection();
		
		StringBuffer sql = new StringBuffer("SELECT a.user_name,a.country FROM user_mst AS a INNER JOIN user_role_mst AS b ON a.user_id = b.user_id WHERE 1=1 ");
		
		sql.append(" AND b.role_id = ").append(valueObject.getRoleId());
		sql.append(" ORDER BY a.user_name ASC ");
		
		
		searchResults = listQuery(connection.prepareStatement(sql.toString()));
		
		closeConnection();
		return searchResults;
	}
	
	protected int databaseUpdate(PreparedStatement stmt) throws SQLException {

		int result = stmt.executeUpdate();

		return result;
	}
	
	protected List<User> listQuery(PreparedStatement stmt) throws SQLException {
		
		ArrayList<User> searchResults = new ArrayList<User>();
		ResultSet result = null;
		openConnection();
		try {
			
			result = stmt.executeQuery();
			
			while (result.next()) {
				User user = createValueObject();

				user.setName(result.getString("user_name"));
				user.setCountry(result.getString("country"));

				searchResults.add(user);
			}

		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
			closeConnection();
		}

		return (List<User>) searchResults;
	}
	
	private void openConnection() {
		try {
			Class.forName(DBConstants.COM_MYSQL_JDBC_DRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			this.connection = DriverManager.getConnection(DBConstants.dbUrl,
					DBConstants.dbUserName, DBConstants.dbPassword);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	private void closeConnection() {
		try {
			this.connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}